{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Pollutant Load Calcs","Lookup Tab"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":1,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Pollutant Load Calcs","index":0,"n_rows":68,"n_cols":7,"rows_kept":68,"cut":false,"hidden":false,"merged":[[61,1,62,1],[61,4,61,5],[16,5,17,5],[41,1,42,1],[41,4,41,6],[22,1,22,2],[41,2,42,3],[43,2,43,3],[44,2,44,3],[45,2,45,3],[61,2,61,3],[61,6,62,6],[18,5,21,5]]}
[null,"City of Bee Cave"]
[null,"Pollutant Load Calculations - Version 4 (06/05/2026)"]
[]
[null,"(Requires User Input)"]
[]
[null,"STEP 1.  ENTER SITE DATA"]
[null,"Project Name:"]
[null,"Drainage Area ID:"]
[null,"Drainage Area to WQC (ac):",5]
[null,"Drainage Area to WQC (sf):",{"$f":"C9*43560","v":217800}]
[null,"Existing Impervious Cover to be left Undisturbed (sf):",10000]
[null,"Existing Impervious Cover to be Redeveloped (sf)",20000]
[null,"New Impervious Cover (sf):",10000]
[null,"Existing Condition Impervious Cover (%):",{"$f":"(C11+C12)/$C$10","v":0.13774104683195593}]
[null,"Undisturbed Existing Condition Impervious Cover (%):",{"$f":"C11/$C$10","v":4.5913682277318638E-2}]
[null,"Redeveloped Existing Condition Impervious Cover (%)",{"$f":"C12/$C$10","v":9.1827364554637275E-2}]
[null,"New Impervious Cover (%):",{"$f":"C13/$C$10","v":4.5913682277318638E-2},null,null,"Advisory Note:"]
[null,"Proposed Open Space Area to be Treated with Herbicides/Pesticides/Fertilizers (sf)",500]
[null,"Proposed Open Space Area to be Treated with Herbicides/Pesticides/Fertilizers (%)",{"$f":"C18/C10","v":2.295684113865932E-3},null,null,{"$f":"IF(C20>8000, \"Water Quality Control must be sized for ALL Impervious Cover - Includes Existing Impervious Cover, Proposed New Impervious Cover, and Redeveloped Impervious Cover.\", \"Water Quality Control may be sized for Proposed New and Redeveloped Impervious Cover Only.\")","v":"Water Quality Control must be sized for ALL Impervious Cover - Includes Existing Impervious Cover, Proposed New Impervious Cover, and Redeveloped Impervious Cover."}]
[null,"Total Impervious Cover (sf)",{"$f":"SUM(C11,C13,C12)","v":40000}]
[null,"Total Impervious Cover (%)",{"$f":"$C$20/$C$10","v":0.18365472910927455}]
[null,"WQV Sizing Check-Point: Impervious Cover (sf) to use in WQV calcs",{"$f":"IF(C20>=8000,C11+C13+C12,C13+C12)","v":40000}]
[null,"*Water Quality Volume sizing check is based on COBC UDC 7.3.2 J.2"]
[]
[null,"STEP 2.  DETERMINE POLLUTANT LOADING"]
[null,"Pollutant","Pre-Developed Loading, Lpre (lb/yr)","Developed Loading, Ldev (lb/yr)"]
[null,"Total Suspended Solids (TSS)",{"$f":"IF($C$14=0,'Lookup Tab'!C4,'Lookup Tab'!D4)*Interpolate('Lookup Tab'!$B$28:$B$48,'Lookup Tab'!$F$28:$F$48,$C$14,0)*$C$9*0.2267","v":225.96943895316807},{"$f":"VLOOKUP($B27,'Lookup Tab'!$B$4:$D$6,3,FALSE)*Interpolate('Lookup Tab'!$B$28:$B$48,'Lookup Tab'!$F$28:$F$48,($C$14+$C$19+$C$17)*100,0)*$C$9*0.2267","v":807.49059892561991}]
[null,"Total Phosphorus (TP)",{"$f":"IF($C$14=0,'Lookup Tab'!C5,'Lookup Tab'!D5)*Interpolate('Lookup Tab'!$B$28:$B$48,'Lookup Tab'!$F$28:$F$48,$C$14,0)*$C$9*0.2267","v":0.53905962545454555},{"$f":"VLOOKUP($B28,'Lookup Tab'!$B$4:$D$6,3,FALSE)*Interpolate('Lookup Tab'!$B$28:$B$48,'Lookup Tab'!$F$28:$F$48,($C$16+$C$19+$C$17)*100,0)*$C$9*0.2267","v":1.5515677745454546}]
[null,"Oil and Grease (O&G)",{"$f":"IF($C$14=0,'Lookup Tab'!C6,'Lookup Tab'!D6)*Interpolate('Lookup Tab'!$B$28:$B$48,'Lookup Tab'!$F$28:$F$48,$C$14,0)*$C$9*0.2267","v":6.8063084022038565},{"$f":"VLOOKUP($B29,'Lookup Tab'!$B$4:$D$6,3,FALSE)*Interpolate('Lookup Tab'!$B$28:$B$48,'Lookup Tab'!$F$28:$F$48,($C$16+$C$19+$C$17)*100,0)*$C$9*0.2267","v":19.590502203856754}]
[null,"*Pollutant loading: Equation 1 from COA ECM 1.6.9.3.B "]
[null,"L = C*V*An*CF"]
[null,"Where:"]
[null,"L = Pollutant load (lb/yr)"]
[null,"C = Pollutant concentration (mg/L), see 'Lookup' tab"]
[null,"V = Annual runoff volume (in/yr), see 'Lookup' tab"]
[null,"An = Area of drainage area (acres), see COA ECM Section 1.6.9.3.B.2"]
[null,"CF = Conversion factor CF (pollutant in mg/L) = 0.2267 (lb-L/mg-Ac-in)"]
[]
[null,"* Zinc is not included in the analysis because it's not a common pollutant linked to development. However, zinc will need to be mitigated for if using crumb rubber for artificial turf installations."]
[]
[null,"STEP 3.  DETERMINE WQC POLLUTANT LOAD REMOVAL EFFICIENCIES"]
[null,"WQC Order \nin Series","WQC Type",null,"Removal Efficiency (%)"]
[null,null,null,null,"TSS","TP","O&G"]
[null,1,"None",null,{"$f":"VLOOKUP($C44,'Lookup Tab'!$B$10:$E$20,2,FALSE)","v":0},{"$f":"VLOOKUP($C44,'Lookup Tab'!$B$10:$E$20,3,FALSE)","v":0},{"$f":"VLOOKUP($C44,'Lookup Tab'!$B$10:$E$20,4,FALSE)","v":0}]
[null,2,"None",null,{"$f":"VLOOKUP($C45,'Lookup Tab'!$B$10:$E$20,2,FALSE)","v":0},{"$f":"VLOOKUP($C45,'Lookup Tab'!$B$10:$E$20,3,FALSE)","v":0},{"$f":"VLOOKUP($C45,'Lookup Tab'!$B$10:$E$20,4,FALSE)","v":0}]
[null,3,"None",null,{"$f":"VLOOKUP($C46,'Lookup Tab'!$B$10:$E$20,2,FALSE)","v":0},{"$f":"VLOOKUP($C46,'Lookup Tab'!$B$10:$E$20,3,FALSE)","v":0},{"$f":"VLOOKUP($C46,'Lookup Tab'!$B$10:$E$20,4,FALSE)","v":0}]
[null,"TOTAL EFFICIENCY",null,null,{"$f":"(1-(1-$E$44/100)*(1-0.65*$E$45/100)*(1-0.25*$E$46/100))*100","v":0},{"$f":"(1-(1-$F$44/100)*(1-0.65*$F$45/100)*(1-0.25*$F$46/100))*100","v":0},{"$f":"(1-(1-$G$44/100)*(1-0.65*$G$45/100)*(1-0.25*$G$46/100))*100","v":0}]
[]
[null,"*TSS removal efficiency and O&G (not explicity stated, but related to TSS) are from TCEQ RG-348 Table 3-1, page 3-4"]
[null,"**TP removal is from LCRA Technical Manual Table 2-6, page 2-11"]
[null,"***Removal Efficiency Equation from COBC ECM Section 1.6.2.G: Water Quality Facilities In Series"]
[]
[]
[]
[null,"Where"]
[null,"ETOT = Total efficiency (%)"]
[null,"E1 = Efficiency of first WQC in series (%)"]
[null,"E2 = Efficiency of second WQC in series (%)"]
[null,"E3 = Efficiency of third WQC in series (%)"]
[]
[null,"STEP 4.  CALCULATE TOTAL POLLUTANT LOAD REMOVAL"]
[null,"Pollutant","Required Removal*",null,"Total WQC Removal**",null,"Meets Criteria?"]
[null,null,"(%)","(lb/yr)","(%)","(lb/yr)"]
[null,"Total Suspended Solids (TSS)",90,{"$f":"IF(AND($C$14=0,$C$17=0),$C27,($C64/100)*($D27-$C27))","v":523.36904397520675},{"$f":"$E$47","v":0},{"$f":"E64/100*D27","v":0},{"$f":"IF(F64>=D64, \"YES\", \"NO\")","v":"NO"}]
[null,"Total Phosphorus (TP)",90,{"$f":"IF(AND($C$14=0,$C$17=0),$C28,($C65/100)*($D28-$C28))","v":0.91125733418181809},{"$f":"$F$47","v":0},{"$f":"E65/100*D28","v":0},{"$f":"IF(F65>=D65, \"YES\", \"NO\")","v":"NO"}]
[null,"Oil and Grease (O&G)",90,{"$f":"IF(AND($C$14=0,$C$17=0),$C29,($C66/100)*($D29-$C29))","v":11.505774421487608},{"$f":"$G$47","v":0},{"$f":"E66/100*D29","v":0},{"$f":"IF(F66>=D66, \"YES\", \"NO\")","v":"NO"}]
[null,"*Removal requirements per Article 7, Section 7.3.2.C of City of Bee Cave UDC (% removal of increased loading from development)"]
[null,"**Amount removed by WQC per year equation: R = Ldev*(ETOT/100)"]

{"sheet":"Lookup Tab","index":1,"n_rows":65,"n_cols":8,"rows_kept":65,"cut":false,"hidden":false,"merged":[[51,1,52,1],[51,3,51,7],[2,4,2,9],[51,2,52,2]]}
[]
[null,"Table 1.  Pollutant Loading Concentrations"]
[null,"Pollutant","Background Concentration,  Cback (mg/L)","Pollutant Concentration, C (mg/L)","Source"]
[null,"Total Suspended Solids (TSS)",166,166,"City of Bee Cave ECM 1.6.9.A"]
[null,"Total Phosphorus (TP)",0.124,0.39600000000000002,"City of Bee Cave ECM 1.6.9.A"]
[null,"Oil and Grease (O&G)",0,5,"City of Bee Cave TCSS Manual Section 5, Page 7 not in current ECM"]
[]
[null,"Table 2.  WQC Removal Efficiency"]
[null," Type","TSS","TP","O&G"]
[null,"None",0,0,{"$f":"C10","v":0}]
[null,"Bioretention",89,72,{"$f":"C11","v":89}]
[null,"Sand Filter",89,55,89]
[null,"Vegetative Filter Strip",85,70,85]
[null,"Infiltration/Trench basin",90,90,{"$f":"C14","v":90}]
[null,"Retention/Irrigation",100,90,100]
[null,"Extended Detention",75,45,75]
[null,"Wet Pond/Wetland Basin",93,65,93]
[null,"Full Infiltration Rain Garden",100,100,100]
[null,"Filterra",85,66,89]
[null,"Jellyfish",85,60,60]
[null,"*TSS removal efficiency and O&G (not explicity stated, but related to TSS) are from TCEQ RG-348 Table 3-1, page 3-4"]
[null,"**TP removal is from LCRA Technical Manual Table 2-6, page 2-11"]
[]
[null,"Note: The COA ECM (1.6.9.3.D.2) gives equations for removal efficiency, rather than standard values, so the actual specs of the WQC would need to be know in order to calculate efficiencies."]
[]
[null,"Table 3. Annual Runoff Coefficient Values for Developed Conditions"]
[null,"Impervious Cover, IC (%)","Runoff-Rainfall Ratio, Rv","Depression Storage, Sd (in)","Annual Number of Runoff Events, θ","Annual Runoff, V (in/yr)"]
[null,0,6.4000000000000001E-2,0.218,46,1.18]
[null,5,0.1,0.19800000000000001,48.4,1.94]
[null,10,0.13600000000000001,0.18,50.6,2.76]
[null,15,0.17199999999999999,0.16300000000000001,52.8,3.63]
[null,20,0.20799999999999999,0.14799999999999999,54.8,4.55]
[null,25,0.24299999999999999,0.13400000000000001,56.7,5.52]
[null,30,0.27900000000000003,0.122,58.5,6.54]
[null,35,0.315,0.11,60.2,7.59]
[null,40,0.35099999999999998,0.1,61.8,8.67]
[null,45,0.38700000000000001,9.0999999999999998E-2,63.2,9.7799999999999994]
[null,50,0.42299999999999999,8.2000000000000003E-2,64.599999999999994,10.91]
[null,55,0.45800000000000002,7.4999999999999997E-2,65.8,12.06]
[null,60,0.49399999999999999,6.8000000000000005E-2,66.900000000000006,13.23]
[null,65,0.53,6.2E-2,68,14.42]
[null,70,0.56599999999999995,5.6000000000000001E-2,69,15.61]
[null,75,0.60199999999999998,5.0999999999999997E-2,69.900000000000006,16.82]
[null,80,0.63700000000000001,4.5999999999999999E-2,70.7,18.03]
[null,85,0.67300000000000004,4.2000000000000003E-2,71.5,19.239999999999998]
[null,90,0.70899999999999996,3.7999999999999999E-2,72.2,20.46]
[null,95,0.745,3.4000000000000002E-2,72.8,21.69]
[null,100,0.78100000000000003,3.1E-2,73.400000000000006,22.91]
[null,"*Source: Table 1-9, COBC ECM 1.6.9.A"]
[]
[null,"Table 4.  COA Effluent Concentrations for Approved WQCs"]
[null,"Pollutant","Unit","Ceff"]
[null,null,null,"Infiltration Field1","Retention Basin2 and Rain Gardens without Underdrain","Rainwater Harvesting2","Sedimentation/Filtration","Biofiltration3"]
[null,"COD (Chemical Oxygen Demand)","mg/L",38.9,43.79,43.79,22.4,22.4]
[null,"Ec (Exherichia coli)","CFU/100 mL",8370,11065,11065,4895,4895]
[null,"Pb (Total Lead)","mg/L",4.28E-3,8.3099999999999997E-3,8.3099999999999997E-3,5.7400000000000003E-3,5.7400000000000003E-3]
[null,"TN (Total Nitrogen)","mg/L",1.19,1.42,1.42,1.07,1.07]
[null,"TOC (Total Organic Carbon)","mg/L",13.03,11.45,11.45,7.33,7.33]
[null,"TP (Total Phosphorus)","mg/L",0.124,0.224,0.224,9.9000000000000005E-2,9.9000000000000005E-2]
[null,"TSS (Total Suspended Solids)","mg/L",166,134,134,20.62,20.62]
[null,"Zn (Zinc)","mg/L",2.3599999999999999E-2,4.53E-2,4.53E-2,2.3E-2,2.3E-2]
[null,"*Source: COA WPD. CM-13-02"]
[null,"1. The infiltrated fraction is assumed to achieve background, or undeveloped, loads."]
[null,"2. These WQCs, given sufficient settling time, are assumed comparable to sedimentation basins."]
[null,"3. These WQCs are assumed comparable to sedimentation/filtration basins."]